Release 10.1A: OpenEdge Data Management:
SQL Development

How OpenEdge SQL interacts with Java

OpenEdge SQL stored procedures allow the use of standard Java programming constructs along with standard SQL statements. To do this, the OpenEdge SQL Engine interacts with Java in the following ways:

When you create a stored procedure, the SQL engine processes the Java code, submits it to the Java compiler, receives the compiled result, and stores the result in the database.
When an application calls a stored procedure, the SQL engine interacts with the Java Virtual Machine (JVM) to execute the stored procedure and receive any result.

Creating stored procedures

The Java source text that makes up the body of a stored procedure is not a complete Java program, but a program fragment or snippet that the OpenEdge SQL Engine converts into a complete Java class when it processes a CREATE PROCEDURE statement. Creating a stored procedure involves the following steps:

A client application or tool issues a CREATE PROCEDURE statement that contains the Java source text.
The OpenEdge SQL Engine adds code to the Java snippet to create a complete Java class and submits the combined code to the Java compiler.
Presuming there are no compilation errors, the Java compiler returns compiled bytecode back to the OpenEdge SQL Engine. If there are compilation errors, the OpenEdge SQL Engine passes the first error message generated by the compiler back to the application or tool that issued the CREATE PROCEDURE statement.
The OpenEdge SQL Engine stores both the Java source text and the bytecode form of the procedure in the database.

Figure 9–1 illustrates the general steps for creating a Java stored procedure.

Figure 9–1: Creating stored procedures

Calling stored procedures

Once a stored procedure is created and stored in the database, any application or other stored procedure can execute it. You can call stored procedures from either ODBC applications or JDBC applications.

Example 9–1 shows an excerpt from an ODBC application that calls a stored procedure (order_parts) using the ODBC syntax { call procedure_name ( param ) }.

Example 9–1: Stored procedure using ODBC syntax

SQLUINTEGER Part_num; SQLINTEGER Part_numInd = 0; // Bind the parameter. SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &Part_num, 0, Part_numInd); // Place the department number in Part_num. Part_num = 318; // Execute the statement. SQLExecDirect(hstmt, "{call order_parts(?)}", SQL_NTS);

A stored procedure executes using the following process:

The application calls the stored procedure through its native calling mechanism. The previous example uses the ODBC call escape sequence.
The OpenEdge SQL retrieves the compiled bytecode form of the procedure and submits it to the Java Virtual Machine for execution.
For every SQL statement in the procedure, the Java Virtual Machine calls OpenEdge SQL.
OpenEdge SQL manages the interaction of the stored procedure with the database and execution of the SQL statements, and returns any result to the Java Virtual Machine.
The Java Virtual Machine returns result (output parameters and result sets) of the procedure to OpenEdge SQL, which in turn passes them to the calling application.

Figure 9–2 illustrates the steps in executing a stored procedure.

Figure 9–2: Executing stored procedures

Using stored procedures

Stored procedures extend the SQL capabilities of a database by adding control through Java program constructs that enforce business rules and perform administrative tasks.

Stored procedures can take advantage of the power of Java programming features. Stored procedures can:

Receive and return input and output parameters.
Handle exceptions.
Include any number and kind of SQL statements to access the database.
Return a procedure result set to the calling application.
Make calls to other procedures.
Use predefined and external Java classes.

OpenEdge SQL supports SQL statements in Java through several classes. See OpenEdge Data Management: SQL Reference for more information.

Table 9–1 summarizes the functionality of these OpenEdge SQL-supplied classes.

Table 9–1: Summary of OpenEdge SQL Java classes 

Functionality	OpenEdge SQL Java class
Immediate (one-time) execution of SQL statements.	SQLIStatement
Prepared (repeated) execution of SQL statements.	SQLPStatement
Retrieval of SQLPrepared (repeated) execution of SQL statements result sets.	SQLCursor
Returning a procedure result set to the application.	DhSQLResultSet
Exception handling for SQL statements.	DhSQLException